# -*- encoding:utf-8 -*-
import datetime
import xlwt
from MysqlHelper import MysqlHelper

"""
zbbix报表导出

## 使用方法：
1. 先安装依赖
pip install pymysql xlwt 
2. 配置数据库和报表保存路径
3. python main.py
# 默认在当前目录生成`zabbix报表.xls`

## 思路:
1. 确定需要的数据
2. 从数据库导出
3. 写入到excel中
4. 发送邮件

## 系统信息
python <= 2.6.6
xlwt == 1.2.0  # python excel write library
mysql == 5.1
zabbix == 2.3

"""


host = '127.0.0.1' # 数据库服务器地址
user = 'root' # 数据库用户名 不要用root用户，授权一个最小导出权限账户
password = 'root' # 数据库密码
dbname = 'zabbix' # 数据库名字
export_path = './'
class ZabbixExport(object):
    def __init__(self):
        self.mysqlHhelp = MysqlHelper(host,user,password,dbname)
        self.mysqlHhelp.open()
        self.data = {}

    def get_freemem(self,gt_date):
        sql = '''
        select from_unixtime(clock,'%%Y-%%m-%%d') as 时间 ,g.name as 组 ,h.name as 主机 ,round(avg(hi.value)/1024/1024/1024,2) as 可用内存GB
        from hosts_groups as hg
        join groups as g on g.groupid=hg.groupid
        join hosts as h on h.hostid=hg.hostid
        join items as i on i.hostid=h.hostid
        join history_uint as hi on hi.itemid=i.itemid
        where h.hostid != 10107 and h.hostid > 10104
        and i.key_ = 'vm.memory.size[available]'
        and from_unixtime(clock) <current_date()
        and from_unixtime(clock) > %s group by h.name order by g.name,h.name ;
'''
        param = gt_date

        self.mysqlHhelp.open()
        result = self.mysqlHhelp.read(sql,param)

        return result

    def get_totalmem(self,gt_date):
        sql = '''select from_unixtime(clock,'%%Y-%%m-%%d') as 时间 ,g.name as 组 ,h.name as 主机 ,round(avg(hi.value)/1024/1024/1024,2) as 总内存GB
                from hosts_groups as hg
                join groups as g on g.groupid=hg.groupid
                join hosts as h on h.hostid=hg.hostid
                join items as i on i.hostid=h.hostid
                join history_uint as hi on hi.itemid=i.itemid
                where h.hostid != 10107 and h.hostid > 10104
                and i.key_ = 'vm.memory.size[total]'
                and from_unixtime(clock) <current_date()
                and from_unixtime(clock) > %s 
                group by h.name order by g.name,h.name;
                '''
        param = gt_date
        self.mysqlHhelp.open()
        result = self.mysqlHhelp.read(sql,param)

        return result

    def get_netin(self,gt_date):
        sql='''
        select from_unixtime(clock,'%%Y-%%m-%%d') as 时间 ,g.name as 组 ,h.name as 主机 ,round(sum(hi.value),2) as '公网流入流量(kbps)'
        from hosts_groups as hg
        join groups as g on g.groupid=hg.groupid
        join hosts as h on h.hostid=hg.hostid
        join items as i on i.hostid=h.hostid
        join history_uint as hi on hi.itemid=i.itemid
        where h.hostid != 10107 and h.hostid > 10104
        and i.key_ = 'net.if.in[eth1]'
        and from_unixtime(clock) <current_date()
        and from_unixtime(clock) > %s 
        group by h.name order by g.name,h.name;
        '''
        param = gt_date

        self.mysqlHhelp.open()
        result = self.mysqlHhelp.read(sql, param)

        return result

    def get_netout(self, gt_date):
        sql = '''
            select from_unixtime(clock,'%%Y-%%m-%%d') as 时间 ,g.name as 组 ,h.name as 主机 ,round(sum(hi.value),2) as '公网流出流量(kbps)'
            from hosts_groups as hg
            join groups as g on g.groupid=hg.groupid
            join hosts as h on h.hostid=hg.hostid
            join items as i on i.hostid=h.hostid
            join history_uint as hi on hi.itemid=i.itemid
            where h.hostid != 10107 and h.hostid > 10104
            and i.key_ = 'net.if.in[eth1]'
            and from_unixtime(clock) <current_date()
            and from_unixtime(clock) > %s 
            group by h.name order by g.name,h.name;
        '''
        param = gt_date

        self.mysqlHhelp.open()
        result = self.mysqlHhelp.read(sql, param)


        return  result

    def get_cpuidel(self, gt_date):
        sql = '''
            select from_unixtime(clock,'%%Y-%%m-%%d') as 时间 ,g.name as 组 ,h.name as 主机 ,round(avg(hi.value),2) as cpu空闲值
            from hosts_groups as hg
            join groups as g on g.groupid=hg.groupid
            join hosts as h on h.hostid=hg.hostid
            join items as i on i.hostid=h.hostid
            join history as hi on hi.itemid=i.itemid
            where h.hostid != 10107 and h.hostid > 10104
            and i.key_ = 'system.cpu.util[,idle]'
            and from_unixtime(clock) <current_date()
            and from_unixtime(clock) > %s group by h.name  order by g.name,h.name;
        '''
        param = gt_date

        self.mysqlHhelp.open()
        result = self.mysqlHhelp.read(sql, param)

        return result

    def _get_collection(self,data,args):
        for i in data:
            # ('date','group','host','value')
            if i[1] in self.data.keys(): # group
                if i[2] in self.data[i[1]].keys(): # host
                    self.data[i[1]][i[2]][args] = int(i[3])
                else:
                    self.data[i[1]][i[2]] = {}
                    self.data[i[1]][i[2]][args] = int(i[3])
            else:
                self.data[i[1]] = {i[2]:{}}
                self.data[i[1]][i[2]][args] = int(i[3])

    def get_data(self):
        gt_date = datetime.date.today() - datetime.timedelta(days=2)
        self._get_collection(self.get_freemem(gt_date),'mem_free')
        self._get_collection(self.get_totalmem(gt_date),'mem_total')
        self._get_collection(self.get_cpuidel(gt_date),'cpu_idle')
        self._get_collection(self.get_netin(gt_date),'net_in')
        self._get_collection(self.get_netout(gt_date),'net_out')

        return self.data

def write_excel(date,data):
    workbook = xlwt.Workbook() # 创建工作簿
    sheet = workbook.add_sheet('%s'%date) # 创建sheet
    title = ['时间','服务器组','服务器','cpu空闲值(单位:%)','总内存(单位:GB)','可用内存(单位:GB)','公网流入流量(单位:kbps)',
             '公网流出流量(单位:kbps)']
    # {'group':{'host':{'mem_free':9}}}
    row_num = 1
    for k,v in enumerate(title): # write title
        sheet.write(0,k,v)
        sheet.col(k).width=256*21 # 设置列宽度

    for group,gv in data.items():
        for host,hv in data[group].items():
            sheet.write(row_num,0,'%s'%date)
            sheet.write(row_num,1,'%s'%group)
            sheet.write(row_num,2,'%s'%host)
            if 'cpu_idle' in hv.keys():
                sheet.write(row_num,3,'%d'%hv['cpu_idle'])
            if 'mem_total' in hv.keys():
                sheet.write(row_num, 4, '%d'%hv['mem_total'])
            if 'mem_free' in hv.keys():
                sheet.write(row_num, 5, '%d'%hv['mem_free'])
            if 'net_in' in hv.keys():
                sheet.write(row_num, 6, '%d'%hv['net_in'])
            if 'net_out' in hv.keys():
                sheet.write(row_num, 7, '%d'%hv['net_out'])

            row_num = row_num + 1

    workbook.save('{}zabbix报表.xls'.format(export_path))

if __name__ == '__main__':
    zabbix_export = ZabbixExport()
    data = zabbix_export.get_data()
    # import json
    # # with open('data.json','w') as f:
    # #     f.write(json.dumps(data))
    # with open('data.json','r') as f:
    #     data = json.load(f)

    yesterday = datetime.date.today() - datetime.timedelta(days=1)
    write_excel(yesterday,data)




